from odoo import api, fields, models, _
import io
import base64
import unicodedata
from odoo.exceptions import UserError
try:
    import xlsxwriter
except Exception:  # pragma: no cover
    xlsxwriter = None


class DocumentsProformaInvoiceWizard(models.TransientModel):
    _name = "documents.proforma_invoice.wizard"
    _description = "形式发票向导"

    language_mode = fields.Selection(
        selection=[("zh", "中文"), ("en", "英文"), ("it", "意大利文")],
        string="语言",
        default="zh",
        required=True,
    )
    export_format = fields.Selection(
        selection=[("xlsx", "XLSX")],
        string="导出格式",
        default="xlsx",
        required=True,
    )
    date = fields.Date(string="日期", default=fields.Date.context_today, required=True)
    company_id = fields.Many2one(
        comodel_name="res.company",
        string="公司",
        default=lambda self: self.env.company,
        required=True,
    )
    partner_id = fields.Many2one("res.partner", string="客户")
    sale_ids = fields.Many2many("sale.order", string="销售订单", required=True)
    currency_id = fields.Many2one("res.currency", string="币种", default=lambda self: self.env.company.currency_id.id)
    manual_rate = fields.Float(string="汇率", help="例如：1 EUR = 8.33 CNY，则填 8.33")
    show_manual_rate = fields.Boolean(string="显示汇率", compute="_compute_show_manual_rate")
    note = fields.Text(string="备注")

    # Generated file for download
    file_name = fields.Char(string="文件名", readonly=True)
    file_data = fields.Binary(string="文件", readonly=True)

    @api.onchange("sale_ids")
    def _onchange_sale_ids(self):
        if self.sale_ids:
            # 若多选客户不一致，不设置客户
            partners = self.sale_ids.mapped("partner_id")
            self.partner_id = partners[0] if len(partners) == 1 else False
            # 币种取第一张销售订单币种，若无则公司币种
            first_sale = self.sale_ids[0]
            self.currency_id = first_sale.currency_id or self.env.company.currency_id

    # ------------------------- Helpers -------------------------
    def _i18n_labels(self, lang):
        labels = {
            "zh": {
                "title": "形式发票",
                "proforma_notice": "PROFORMA INVOICE",
                "date": "发票日期",
                "number": "编号",
                "seq": "序号",
                "product": "产品",
                "desc": "描述",
                "qty": "数量",
                "unit_price": "单价",
                "amount": "金额",
                "tax": "税项",
                "total_qty": "总数量",
                "subtotal": "小计",
                "tax_total": "税项",
                "grand_total": "含税总额",
                "company": "公司",
                "customer": "客户",
                "salesperson": "销售员",
                "currency": "币种",
                "exchange_rate": "汇率",
                "sales_order": "销售订单",
            },
            "en": {
                "title": "Proforma Invoice",
                "proforma_notice": "PROFORMA INVOICE",
                "date": "Invoice Date",
                "number": "Number",
                "seq": "Seq",
                "product": "Product",
                "desc": "Description",
                "qty": "Quantity",
                "unit_price": "Unit Price",
                "amount": "Amount",
                "tax": "VAT Rate",
                "total_qty": "Total Quantity",
                "subtotal": "Subtotal",
                "tax_total": "Tax",
                "grand_total": "Total",
                "company": "Company",
                "customer": "Customer",
                "salesperson": "Salesperson",
                "currency": "Currency",
                "exchange_rate": "Exchange Rate",
                "sales_order": "Sales Order",
            },
            "it": {
                "title": "Fattura Proforma",
                "proforma_notice": "FATTURA PROFORMA",
                "date": "Data Fattura",
                "number": "Numero",
                "seq": "N°",
                "product": "Prodotto",
                "desc": "Descrizione",
                "qty": "Quantità",
                "unit_price": "Prezzo unitario",
                "amount": "Importo",
                "tax": "IVA",
                "total_qty": "Quantità totale",
                "subtotal": "Imponibile",
                "tax_total": "IVA",
                "grand_total": "Totale",
                "company": "Azienda",
                "customer": "Cliente",
                "salesperson": "Venditore",
                "currency": "Valuta",
                "exchange_rate": "Tasso di cambio",
                "sales_order": "Ordine di vendita",
            },
        }
        return labels.get(lang or "zh")

    def _display_width(self, text):
        s = str(text or "")
        width = 0
        for ch in s:
            eaw = unicodedata.east_asian_width(ch)
            width += 2 if eaw in ("F", "W") else 1
        return width

    def _map_to_odoo_lang(self, lang_code):
        mapping = {
            "zh": "zh_CN",
            "en": "en_US",
            "it": "it_IT",
        }
        return mapping.get(lang_code, "zh_CN")

    def _get_valid_odoo_lang(self, lang_code):
        """Return lang_code only if installed in this DB; else return None."""
        if not lang_code:
            return None
        Lang = self.env["res.lang"]
        rec = Lang.search([("code", "=", lang_code)], limit=1)
        return rec.code if rec else None

    def _get_tax_rate_percent(self, line):
        percent = 0.0
        for tax in line.tax_id:
            if getattr(tax, "amount_type", "percent") == "percent":
                percent += tax.amount
        return percent

    def _get_exchange_rate(self):
        """Get exchange rate for display"""
        if self.manual_rate:
            return self.manual_rate
        
        # Get rate from sale order currency to company currency
        if self.currency_id and self.currency_id != self.company_id.currency_id:
            # Try to get rate from sale order
            if hasattr(self.sale_id, 'currency_rate') and self.sale_id.currency_rate:
                return self.sale_id.currency_rate
            # Fallback to current rate
            return self.currency_id._get_conversion_rate(
                self.currency_id, 
                self.company_id.currency_id, 
                self.company_id, 
                self.date
            )
        return 1.0

    @api.depends("currency_id", "company_id")
    def _compute_show_manual_rate(self):
        for wizard in self:
            wizard.show_manual_rate = bool(
                wizard.currency_id
                and wizard.company_id
                and wizard.currency_id != wizard.company_id.currency_id
            )

    # ------------------------- Export -------------------------
    def action_export(self):
        self.ensure_one()
        if self.export_format != "xlsx":
            return {"type": "ir.actions.act_window_close"}
        if not xlsxwriter:
            raise UserError(_("The Python package 'xlsxwriter' is required to export XLSX files."))
        # 校验选择
        if not self.sale_ids:
            raise UserError(_("Please select at least one sales order."))

        # 校验客户一致
        partners = self.sale_ids.mapped("partner_id")
        if len(partners) > 1:
            raise UserError(_("所选销售订单的客户不一致，无法合并导出形式发票。请仅选择同一客户的销售订单。"))

        # 校验币种一致
        currencies = self.sale_ids.mapped("currency_id")
        if len(currencies) > 1:
            raise UserError(_("所选销售订单的币种不一致，无法合并导出同一份形式发票。请仅选择相同币种的订单。"))

        lang = self.language_mode or "zh"
        L = self._i18n_labels(lang)
        odoo_lang = self._get_valid_odoo_lang(self._map_to_odoo_lang(lang))

        # 汇总所有订单明细（跳过分隔/备注行）
        order_lines = self.sale_ids.mapped("order_line").filtered(lambda l: not l.display_type)

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})
        sheet = workbook.add_worksheet(L["title"])  # localized sheet name
        
        # 打印设置：A4 纵向，按页宽缩放，水平居中，边距
        try:
            sheet.set_portrait()
            sheet.set_paper(9)  # A4
            sheet.fit_to_pages(1, 0)
            sheet.center_horizontally()
            sheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
        except Exception:
            pass

        # Styles
        fmt_title = workbook.add_format({"bold": True, "font_size": 20, "align": "center", "valign": "vcenter"})
        fmt_proforma_notice = workbook.add_format({"bold": True, "font_size": 14, "align": "center", "valign": "vcenter", "color": "red"})
        fmt_subtitle = workbook.add_format({"bold": True, "font_size": 12})
        fmt_header = workbook.add_format({"bold": True, "align": "center", "valign": "vcenter", "border": 1})
        fmt_cell = workbook.add_format({"align": "left", "valign": "vcenter", "border": 1})
        fmt_cell_center = workbook.add_format({"align": "center", "valign": "vcenter", "border": 1})
        fmt_cell_left = workbook.add_format({"align": "left", "valign": "vcenter"})
        
        # Currency-aware formats
        cur = self.currency_id or self.company_id.currency_id
        symbol = (cur and cur.symbol) or ""
        position = (cur and getattr(cur, "position", "before")) or "before"
        symbol_escaped = (symbol or "").replace('"', '""')
        if position == "after":
            num_format_curr = f"#,##0.00\"{symbol_escaped}\""
        else:
            num_format_curr = f"\"{symbol_escaped}\"#,##0.00"
        fmt_num = workbook.add_format({"num_format": num_format_curr, "align": "right", "valign": "vcenter", "border": 1})
        fmt_tot_label = workbook.add_format({"align": "right", "bold": True})
        fmt_tot_num = workbook.add_format({"num_format": num_format_curr, "align": "right", "bold": True, "right": 1, "right_color": "#000000"})
        fmt_note = workbook.add_format({"italic": True})

        # -------- 发票样式所需的额外样式（与 invoice_wizard 对齐） --------
        company_header_nobottom_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 14,
            'top': 1,
            'left': 1,
            'right': 1,
            'top_color': '#000000',
            'left_color': '#000000',
            'right_color': '#000000'
        })
        company_info_nobottom_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'left': 1,
            'right': 1,
            'left_color': '#000000',
            'right_color': '#000000'
        })
        company_info_notop_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'left': 1,
            'right': 1,
            'bottom': 1,
            'left_color': '#000000',
            'right_color': '#000000',
            'bottom_color': '#000000'
        })
        date_invoice_format = workbook.add_format({
            'align': 'right',
            'font_size': 11,
            'right': 1,
            'border_color': '#000000',
            'right_color': '#000000'
        })
        # 左/右边框辅助样式
        left_column_border_format = workbook.add_format({'left': 1, 'left_color': '#000000'})
        right_column_border_format = workbook.add_format({'right': 1, 'right_color': '#000000'})
        right_only_border_format = workbook.add_format({'right': 1, 'right_color': '#000000'})
        # 客户信息样式
        client_label_left_format = workbook.add_format({'bold': True, 'font_size': 11, 'left': 1, 'left_color': '#000000'})
        client_info_left_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'left_color': '#000000'})
        client_info_left_right_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'left_color': '#000000'})
        client_info_format = workbook.add_format({'font_size': 10})
        # 分隔线
        header_separator_format = workbook.add_format({'bottom': 2, 'bottom_color': '#000000'})
        # 表头首列样式（带左边框）
        table_header_left_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'border': 1,
            'left': 1,
            'left_color': '#000000'
        })
        # 底部信息样式
        bottom_info_left_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'left_color': '#000000'})
        bottom_info_left_right_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'left_color': '#000000'})
        address_bank_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'bottom': 1, 'left_color': '#000000'})

        # 关闭网格线
        try:
            sheet.hide_gridlines(2)
        except Exception:
            pass

        # Exchange rate for display calculation
        display_rate = self.manual_rate or 1.0

        # Column widths (dynamic based on headers and data) — remove Seq column, add Currency after Qty
        headers_w = [L["number"], L["desc"], L["qty"], L["currency"], L["unit_price"], L["tax"], L["amount"]]
        base_widths = [18, 36, 12, 10, 14, 10, 16]
        caps = [28, 60, 14, 14, 20, 12, 24]
        header_widths = [max(base_widths[i], self._display_width(h) + 2) for i, h in enumerate(headers_w)]

        # Pre-scan data to estimate widths
        max_idx_w = self._display_width(str(max(1, len(order_lines)))) + 2
        max_code_w = 0
        max_desc_w = 0
        max_qty_w = 0
        max_tax_w = self._display_width("100%") + 2
        max_cur_w = self._display_width((cur and cur.name) or "") + 2
        max_unit_w = 0
        max_amt_w = 0
        for line in order_lines:
            line_serial = getattr(line, "new_serial_number", None)
            # 修改产品编码获取逻辑：优先条形码，然后line_serial，然后default_code
            product_code = line.product_id.product_tmpl_id.barcode or line_serial or (line.product_id.default_code or "")
            if odoo_lang:
                desc = line.product_id.with_context(lang=odoo_lang).name or (line.name or "")
            else:
                desc = line.name or (line.product_id.name or "")
            qty = line.product_uom_qty
            unit_price = line.price_unit
            tax_rate = self._get_tax_rate_percent(line)
            amount = line.price_total

            max_code_w = max(max_code_w, self._display_width(product_code) + 2)
            max_desc_w = max(max_desc_w, self._display_width(desc) + 2)
            max_qty_w = max(max_qty_w, self._display_width(f"{qty:,.2f}") + 2)
            unit_str = f"{abs(unit_price * display_rate):,.2f}"
            amt_str = f"{abs(amount * display_rate):,.2f}"
            if position == "after":
                unit_disp = f"{unit_str}{symbol}"
                amt_disp = f"{amt_str}{symbol}"
            else:
                unit_disp = f"{symbol}{unit_str}"
                amt_disp = f"{symbol}{amt_str}"
            max_unit_w = max(max_unit_w, self._display_width(unit_disp) + 2)
            max_amt_w = max(max_amt_w, self._display_width(amt_disp) + 2)
            max_tax_w = max(max_tax_w, self._display_width(f"{tax_rate:.0f}%") + 2)

        data_widths = [max_code_w, max_desc_w, max_qty_w, max_cur_w, max_unit_w, max_tax_w, max_amt_w]
        final_widths = [min(max(header_widths[i], data_widths[i], base_widths[i]), caps[i]) for i in range(7)]
        for col_idx, width in enumerate(final_widths):
            col_letter = chr(ord("A") + col_idx)
            sheet.set_column(f"{col_letter}:{col_letter}", width)

        # -------- 顶部公司信息（与发票样式一致） --------
        company = self.company_id
        # A1:G1 公司名称
        sheet.merge_range('A1:G1', (company.display_name or company.name), company_header_nobottom_format)
        # A2:G2 公司地址（street）
        sheet.merge_range('A2:G2', (company.partner_id and (company.partner_id.street or '')) or '', company_info_nobottom_format)
        # A3:G3 邮编+城市+ITALIA
        city_zip = f"{(company.partner_id and (company.partner_id.zip or '')) or ''} {(company.partner_id and (company.partner_id.city or '')) or ''} ITALIA".strip()
        sheet.merge_range('A3:G3', city_zip, company_info_nobottom_format)
        # A4:F4 公司VAT
        if company.vat:
            vat_label_top = {'zh': '增值税号', 'en': 'VAT', 'it': 'PARTITA IVA'}.get(self.language_mode, 'VAT')
            sheet.merge_range('A4:G4', f"{vat_label_top}: {company.vat}", company_info_notop_format)

        # 右上角日期与编号（使用形式发票的 label）
        try:
            sheet.write('G5', f"{L['date'].upper()}: {self.date.strftime('%d/%m/%Y')}", date_invoice_format)
        except Exception:
            sheet.write('G5', f"{L['date'].upper()}: {fields.Date.to_string(self.date)}", date_invoice_format)
        order_names = ", ".join(self.sale_ids.mapped('name')) if self.sale_ids else ''
        sheet.write('G6', f"{L['number'].upper()}: {order_names}", date_invoice_format)

        # 左侧补齐边框；设置F列右边框
        sheet.write_blank('A5', None, left_column_border_format)
        sheet.write_blank('A6', None, left_column_border_format)
        sheet.set_column('G:G', 15, right_column_border_format)

        # 页眉分隔线
        sheet.set_row(6, 3)
        sheet.merge_range('A7:G7', '', header_separator_format)

        # -------- 客户信息（与发票样式一致） --------
        client = self.partner_id or (self.sale_ids and self.sale_ids[0].partner_id)
        sheet.write('A8', f"{L['customer'].upper()}:", client_label_left_format)
        sheet.write_blank('G8', None, right_only_border_format)
        # 客户名称
        sheet.merge_range('A9:D9', (client and client.name) or '', client_info_left_border_format)
        sheet.write_blank('G9', None, right_only_border_format)
        # 地址行
        row = 10
        address_parts = []
        if client and getattr(client, 'street', False):
            address_parts.append(client.street)
        if client and getattr(client, 'city', False):
            address_parts.append(client.city)
        if client and getattr(client, 'state_id', False) and client.state_id and getattr(client.state_id, 'name', False):
            address_parts.append(client.state_id.name)
        if client and getattr(client, 'country_id', False) and client.country_id and getattr(client.country_id, 'name', False):
            address_parts.append(client.country_id.name)
        if any(address_parts):
            address_label = {'zh': '地址', 'en': 'Address', 'it': 'Indirizzo'}.get(self.language_mode, 'Address')
            sheet.merge_range(f'A{row}:G{row}', f"{', '.join([p for p in address_parts if p])}", client_info_left_right_border_format)
            row += 1
        # 客户 VAT
        if client and client.vat:
            vat_label = {'zh': '增值税号', 'en': 'VAT', 'it': 'PARTITA IVA'}.get(self.language_mode, 'VAT')
            sheet.merge_range(f'A{row}:D{row}', f"{client.vat}", client_info_left_border_format)
            sheet.write_blank(f'G{row}', None, right_only_border_format)
            row += 1
        # 上一行添加左边框
        sheet.write_blank(f'A{row}', None, left_column_border_format)
        # 打印币种（不打印汇率）
        # try:
        #     current_currency = (self.currency_id or self.company_id.currency_id)
        #     currency_code = (current_currency and current_currency.name) or ''
        #     sheet.write(f'D{row}', f"{L['currency'].upper()}: {currency_code}", client_info_format)
        # except Exception:
        #     pass
        # 币种的下一行补齐左边框
        sheet.write_blank(f'A{row + 1}', None, left_column_border_format)
        # 表格起始行
        table_start_row = row + 1

        # Table headers (remove Seq column)
        headers = [L["number"], L["desc"], L["qty"], L["currency"], L["unit_price"], L["tax"], L["amount"]]
        for col, head in enumerate(headers):
            header_fmt = table_header_left_format if col == 0 else fmt_header
            sheet.write(table_start_row, col, head, header_fmt)
        row = table_start_row + 1

        # Body（按 编号/描述/单价 合并）
        total_qty = 0.0
        # 分组保持顺序
        groups = {}
        group_keys = []
        for line in order_lines:
            line_serial = getattr(line, 'new_serial_number', None)
            product_code = line.product_id.product_tmpl_id.barcode or line_serial or (line.product_id.default_code or "")
            if odoo_lang:
                description = line.product_id.with_context(lang=odoo_lang).name or (line.name or "")
            else:
                description = line.name or (line.product_id.name or "")
            unit_price = line.price_unit
            tax_rate = self._get_tax_rate_percent(line)
            key = (product_code, description, unit_price)
            if key not in groups:
                groups[key] = {
                    'qty': 0.0,
                    'amount': 0.0,
                    'tax_rates': set(),
                    'product_code': product_code,
                    'description': description,
                    'unit_price': unit_price,
                }
                group_keys.append(key)
            g = groups[key]
            g['qty'] += line.product_uom_qty
            g['amount'] += line.price_total
            g['tax_rates'].add(tax_rate)
            total_qty += line.product_uom_qty

        for idx, key in enumerate(group_keys, start=1):
            g = groups[key]
            tax_label = f"{next(iter(g['tax_rates'])):.0f}%" if len(g['tax_rates']) == 1 else "-"
            sheet.write(row, 0, g['product_code'], fmt_cell)
            sheet.write(row, 1, g['description'], fmt_cell)
            sheet.write_number(row, 2, g['qty'], fmt_cell_center)
            sheet.write(row, 3, (cur and cur.name) or '', fmt_cell_center)
            sheet.write_number(row, 4, g['unit_price'] * display_rate, fmt_num)
            sheet.write(row, 5, tax_label, fmt_cell_center)
            sheet.write_number(row, 6, g['amount'] * display_rate, fmt_num)
            row += 1

        # 已移除“总数量”打印

        # Totals box
        subtotal = sum(order_lines.mapped("price_subtotal")) * display_rate
        tax_amount = sum(order_lines.mapped("price_tax")) * display_rate
        grand_total = sum(order_lines.mapped("price_total")) * display_rate

        totals_row = table_start_row + max(1, len(group_keys)) + 2
        label_col = 5
        value_col = 6
        sheet.write(totals_row, label_col, L["subtotal"], fmt_tot_label)
        sheet.write_number(totals_row, value_col, subtotal, fmt_tot_num)
        sheet.write(totals_row + 1, label_col, L["tax_total"], fmt_tot_label)
        sheet.write_number(totals_row + 1, value_col, tax_amount, fmt_tot_num)
        sheet.write(totals_row + 2, label_col, L["grand_total"], fmt_tot_label)
        sheet.write_number(totals_row + 2, value_col, grand_total, fmt_tot_num)

        # 连续左边框：从小计上一行开始到下五行（A列）使用数值坐标避免 A1/A0 混淆
        try:
            # totals_row 为0基行号+1的字符串用法，我们需要换算为0基行号
            # 此处我们通过表格起始行与分组行数推导0基行号
            # table_start_row 已是整数，totals_row_int 等于当前行号（1基）=> 0基为 totals_row_int-1
            # 但我们无法直接取0基，用 write_blank(row_idx, col_idx, None, fmt)
            for i in range(0, 6):
                sheet.write_blank(totals_row + i, 0, None, left_column_border_format)
        except Exception:
            pass

        if self.note:
            sheet.write(totals_row + 4, 0, self.note, fmt_note)

        # -------- 尾部打印银行信息与 VAT 条款（与发票样式一致） --------
        footer_row = totals_row + 6
        # VAT 条款
        sheet.merge_range(f'A{footer_row}:G{footer_row}', "VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72", bottom_info_left_right_format)
        # VAT 下一行补齐左边框（空行）使用数值坐标
        sheet.write_blank(footer_row, 0, None, left_column_border_format)
        # 空一行
        footer_row += 2
        # BANK INFO
        sheet.write(f'A{footer_row}', "BANK NAME:", bottom_info_left_border_format)
        sheet.write_blank(f'G{footer_row}', None, right_only_border_format)
        footer_row += 1
        sheet.write(f'A{footer_row}', "Bank Name: Unicredit", bottom_info_left_border_format)
        sheet.write_blank(f'G{footer_row}', None, right_only_border_format)
        footer_row += 1
        sheet.write(f'A{footer_row}', "BIC/SWFT: UNCRITM1MN5", bottom_info_left_border_format)
        sheet.write_blank(f'G{footer_row}', None, right_only_border_format)
        footer_row += 1
        sheet.write(f'A{footer_row}', "IBAN: IT03L0200812932000106768229", bottom_info_left_border_format)
        sheet.write_blank(f'G{footer_row}', None, right_only_border_format)
        footer_row += 1
        sheet.merge_range(f'A{footer_row}:G{footer_row}', "Address Bank: Via Marzabotto 75, 41100,Modena", address_bank_border_format)

        workbook.close()
        output.seek(0)
        data = output.read()

        order_names_for_file = "_".join(self.sale_ids.mapped("name")) if self.sale_ids else "orders"
        filename = f"{L['title']}_{order_names_for_file}.xlsx"
        self.write({
            "file_name": filename,
            "file_data": base64.b64encode(data),
        })

        return {
            "type": "ir.actions.act_url",
            "url": (
                f"/web/content/?model={self._name}&id={self.id}"
                f"&field=file_data&filename_field=file_name&download=true"
            ),
            "target": "self",
        } 